﻿Imports MySql.Data.MySqlClient
Public Class personal3_2_1

    Dim start_date As Date
    Dim end_date As Date
    Dim course_id_sel As String
    Public SQL As MySqlConnection
    Public couse_id As String
    Public couse_name As String
    Public key As String
    Public newname3_2 As String

    Public time_start As String
    Public time_end As String
    Public test As String
    Public newcode3_2 As String

    Dim date_split() As String
    Dim date_split2() As String
    Dim start_date_hh As String
    Dim start_date_mm As String
    Dim end_date_hh As String
    Dim end_date_mm As String



    Public Sub New(ByRef SQLCon As MySqlConnection, ByVal course_name As String, ByVal trainer As String, ByVal date_train As String, ByVal duration As String, ByVal course_cost As String, ByVal employee_name As String, ByVal employee_code As String, ByVal training_type As String)


        date_split = Split(date_train, "-")

        date_split2 = Split(date_split(0), ".")
        start_date_hh = date_split2(0)
        start_date_mm = date_split2(1)

        date_split2 = Split(date_split(1), ".")
        end_date_hh = date_split2(0)
        end_date_mm = date_split2(1)




        start_date = "#12/10/3000 " + start_date_hh + ":" + start_date_mm + ":00#"
        end_date = "#12/10/3000 " + end_date_hh + ":" + end_date_mm + ":00#"


        InitializeComponent()
        SQL = SQLCon

        txt_course.Text = course_name
        txt_name.Text = employee_name
        txt_codename.Text = employee_code
        txt_cost.Text = course_cost
        txt_trainer.Text = trainer
        txt_duration.Text = duration
        course_type.Text = training_type

        'MsgBox(newcode3_2)
    End Sub

    ' Private Sub personal3_2_1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
    'Dim mainForm As main = New main()
    '   mainForm.Show()
    'Me.Close()
    'End Sub



    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Dim AddForm As txt_totalCost = New txt_totalCost(SQL, newname3_2, newcode3_2, couse_id, couse_name)
        AddForm.Show()
        Me.Close()
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim AddForm As txt_totalCost = New txt_totalCost(SQL, txt_name.Text, txt_codename.Text, course_id_sel, txt_course.Text)
        AddForm.Show()
        Me.Close()

    End Sub

    Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub personal3_2_1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        SQL.Open()

        Dim mysqlCon1 As MySqlConnection

        mysqlCon1 = New MySqlConnection

        mysqlCon1.ConnectionString = "server=192.168.1.13;port = 3308;user id=" + "root" + ";password=" + "pisona" + ";database=testremote;Character Set =utf8"
        mysqlCon1.Open()

        Dim sel_course_mysqlda As New MySql.Data.MySqlClient.MySqlDataAdapter
        Dim sel_course_mysqldr As MySql.Data.MySqlClient.MySqlDataReader
        Dim sel_course_mysqlAdapter As New MySqlCommand

        Dim sel_training_mysqlda As New MySql.Data.MySqlClient.MySqlDataAdapter
        Dim sel_training_mysqldr As MySql.Data.MySqlClient.MySqlDataReader
        Dim sel_training_mysqlAdapter As New MySqlCommand


        Dim select_course As String
        Dim date_string As String
        Dim split_date() As String

        select_course = "Select * from course where course_name Like '" + txt_course.Text + "'"
        sel_course_mysqlAdapter.CommandText = select_course
        sel_course_mysqlAdapter.Connection = SQL
        sel_course_mysqlda.SelectCommand = sel_course_mysqlAdapter
        sel_course_mysqldr = sel_course_mysqlAdapter.ExecuteReader


        While sel_course_mysqldr.Read()
            course_id_sel = sel_course_mysqldr("course_id")
        End While

        sel_training_mysqlAdapter.CommandText = "Select training_date from training where course_id = '" + course_id_sel + "'and emp_code = '" + txt_codename.Text + "'"
        sel_training_mysqlAdapter.Connection = mysqlCon1
        sel_training_mysqlda.SelectCommand = sel_training_mysqlAdapter
        sel_training_mysqldr = sel_training_mysqlAdapter.ExecuteReader

        While sel_training_mysqldr.Read()
            date_string = sel_training_mysqldr("training_date")
        End While

        'split_date = Split(date_string, "-")

        DateTimePicker1.Value = "#" + date_string + " 00:00#"
        DateTimePicker2.Value = start_date
        DateTimePicker3.Value = end_date

        SQL.Close()


    End Sub

    Private Sub DateTimePicker2_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker2.ValueChanged
        RecalculateTime()
    End Sub

    Private Sub DateTimePicker3_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker3.ValueChanged
        RecalculateTime()
    End Sub

    Public Sub addZero(ByVal hours1 As String, ByVal min1 As String, ByVal hours2 As String, ByVal min2 As String)


        If (Len(hours1) < 2) Then
            time_start = "0" + hours1
        Else
            time_start = hours1
        End If

        If (Len(min1) < 2) Then
            time_start = time_start + ".0" + min1
        Else
            time_start = time_start + "." + min1
        End If

        If (Len(hours2) < 2) Then
            time_end = "0" + hours2
        Else
            time_end = hours2
        End If
        If (Len(min2) < 2) Then
            time_end = time_end + ".0" + min2
        Else
            time_end = time_end + "." + min2
        End If
    End Sub

    Private Sub RecalculateTime()

        'Dim date1 As TimeSpan
        'Dim sum As String
        Dim dateTime As Date
        Dim dateTime2 As Date
        'Dim datetime_sub As Date
        'Dim hours As String
        Dim full_hours As Integer = 1440
        Dim int_hours As Integer
        Dim int_min As Integer

        'Dim result_hours As Integer
        Dim result_min As String

        Dim tempmin1 As Integer
        Dim tempmin2 As Integer

        Dim tempresultmin As Integer

        'datetime_sub = #12:00:00 AM#

        'Dim test As String
        dateTime = DateTimePicker2.Value
        dateTime2 = DateTimePicker3.Value

        test = dateTime2.Subtract(dateTime).ToString
        'MsgBox(test)

        Dim test1 As String
        test1 = Mid(test, 1, 1)
        ''MsgBox(test)
        '' MsgBox(test1)

        '' MsgBox(time_start)
        ''MsgBox(time_end)

        addZero(dateTime.Hour.ToString, dateTime.Minute.ToString, dateTime2.Hour.ToString, dateTime2.Minute.ToString)




        If test1 = "-" Then

            tempmin1 = CInt(dateTime.Hour.ToString()) * 60 + CInt(dateTime.Minute.ToString())
            tempmin2 = CInt(dateTime2.Hour.ToString()) * 60 + CInt(dateTime2.Minute.ToString())

            tempresultmin = tempmin2 - tempmin1

            tempresultmin = (24 * 60) + tempresultmin

            int_min = tempresultmin Mod 60

            int_hours = tempresultmin \ 60

            result_min = (dateTime.Minute - dateTime2.Minute)



            If (Len(int_hours.ToString) = 1) Then

                ''   MsgBox(txt_duration.Text)


                If (Len(int_min.ToString) = 1) Then

                    txt_duration.Text = "0" + int_hours.ToString + ".0" + int_min.ToString

                Else
                    txt_duration.Text = "0" + int_hours.ToString + "." + int_min.ToString
                End If


            ElseIf (Len(int_min.ToString) = 1) Then
                'addZero(int_min.ToString)
                txt_duration.Text = int_hours.ToString + ".0" + int_min.ToString

            Else

                txt_duration.Text = int_hours.ToString + "." + int_min.ToString


            End If


        Else

            tempmin1 = CInt(dateTime.Hour.ToString()) * 60 + CInt(dateTime.Minute.ToString())
            tempmin2 = CInt(dateTime2.Hour.ToString()) * 60 + CInt(dateTime2.Minute.ToString())

            tempresultmin = tempmin2 - tempmin1

            ''tempresultmin = tempresultmin + 60

            int_min = tempresultmin Mod 60

            int_hours = tempresultmin \ 60

            result_min = (dateTime2.Minute - dateTime.Minute)

            '' If (dateTime.Minute < dateTime2.Minute) Then

            ''If result_min > 30 Then
            ''int_hours = int_hours - 1
            ''End If
            ''End If

            If (Len(int_hours.ToString) = 1) Then

                ''   MsgBox(txt_duration.Text)


                If (Len(int_min.ToString) = 1) Then
                    txt_duration.Text = "0" + int_hours.ToString + ".0" + int_min.ToString

                Else
                    txt_duration.Text = "0" + int_hours.ToString + "." + int_min.ToString
                End If


            ElseIf (Len(int_min.ToString) = 1) Then
                'addZero(int_min.ToString)
                txt_duration.Text = int_hours.ToString + ".0" + int_min.ToString

            Else

                txt_duration.Text = int_hours.ToString + "." + int_min.ToString


            End If


            ''txt_duration.Text = test

        End If

        ''txt_duration.Text = trainingHour
        ''Label18.Text = trainingMin


    End Sub

    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim sel_course_mysqlda As New MySql.Data.MySqlClient.MySqlDataAdapter
        Dim sel_course_mysqldr As MySql.Data.MySqlClient.MySqlDataReader
        Dim sel_course_mysqlAdapter As New MySqlCommand


        Dim up_training_mysqlda As New MySql.Data.MySqlClient.MySqlDataAdapter
        Dim up_training_mysqldr As MySql.Data.MySqlClient.MySqlDataReader
        Dim up_training_mysqlAdapter As New MySqlCommand

        Dim mysqlCon As MySqlConnection
        Dim mysqlCon1 As MySqlConnection
        Dim checkedApprove As String
        Dim select_course As String
        Dim update_training As String
        Dim course_id As String

        mysqlCon = New MySqlConnection
        mysqlCon1 = New MySqlConnection

        mysqlCon.ConnectionString = "server=192.168.1.13;port = 3308;user id=" + "root" + ";password=" + "pisona" + ";database=testremote;Character Set =utf8"
        mysqlCon1.ConnectionString = "server=192.168.1.13;port = 3308;user id=" + "root" + ";password=" + "pisona" + ";database=testremote;Character Set =utf8"
        mysqlCon.Open()
        mysqlCon1.Open()

        If txt_codename.Text <> "" And txt_cost.Text <> "" And txt_course.Text <> "" And txt_name.Text <> "" And txt_trainer.Text <> "" Then


            select_course = "Select course_id from course where course_name = '" + txt_course.Text + "' and course_trainer = '" & txt_trainer.Text & "'"
            sel_course_mysqlAdapter.CommandText = select_course
            sel_course_mysqlAdapter.Connection = mysqlCon
            sel_course_mysqlda.SelectCommand = sel_course_mysqlAdapter
            sel_course_mysqldr = sel_course_mysqlAdapter.ExecuteReader


            While sel_course_mysqldr.Read()
                couse_id = sel_course_mysqldr("course_id")
            End While

            If CheckBox1.Checked = True Then
                checkedApprove = 1
            Else
                checkedApprove = 0
            End If
            addZero(DateTimePicker2.Value.Hour.ToString, DateTimePicker2.Value.Minute.ToString, DateTimePicker3.Value.Hour.ToString, DateTimePicker3.Value.Minute.ToString)

            up_training_mysqlAdapter.CommandText = "Update  training SET time_start = '" & time_start & "',time_end = '" & time_end & "', training_hours = '" & txt_duration.Text & "',training_approve = '" & checkedApprove & "', training_cost_person = '" & txt_cost.Text & "',training_type ='" + course_type.Text + "' where course_id like '" & couse_id & "' and emp_code like '" & txt_codename.Text & "'"
            up_training_mysqlAdapter.Connection = mysqlCon1
            up_training_mysqlAdapter.ExecuteNonQuery()

        End If

        Dim AddForm As txt_totalCost = New txt_totalCost(SQL, txt_name.Text, txt_codename.Text, course_id_sel, txt_course.Text)
        AddForm.Show()
        Me.Close()

    End Sub
End Class